﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;
namespace DAL
{
  public     class StateDAL
  {
      /// <summary>
      /// 链接字符串
      /// </summary>
      SqlConnection con = new SqlConnection(" Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;Pwd=123456");
      /// <summary>
      /// 状态显示功能
      /// </summary>
      /// <returns></returns>
      public List<State> State_Show()
      {
          con.Open();
          string sql = "select * from State";
          SqlDataAdapter adp = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<State> list = new List<State>();
          foreach (DataRow item in table.Rows)
          {
              State s = new State();
              s.StateId = Convert.ToInt32(item["StateId"]);
              s.StateName = item["StateName"].ToString();
              list.Add(s);
          }
          return list;

      }
      /// <summary>
      /// 状态的添加
      /// </summary>
      /// <param name="m"></param>
      /// <returns></returns>
      public int State_Insert(State s)
      {
          con.Open();
          string sql = "inset into State values ('" + s.StateId + "','" + s.StateName + "')";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }
      /// <summary>
      /// 状态的删除
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int State_Delete(int id)
      {
          con.Open();
          string sql = "Delete from State where StateId='" + id + "'";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }


      /// <summary>
      /// 状态的显示或修改第一步
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public State State_details(int id)
      {
          con.Open();
          string sql = "select StateId ,StateName from State Where StateId='" + id + "'";
          SqlDataAdapter adp = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<State> list = new List<State>();
          foreach (DataRow item in table.Rows)
          {
              State s = new State();
              s.StateId = Convert.ToInt32(item["StateId"]);
              s.StateName =item["StateName"].ToString();
              list.Add(s);
          }
          return list.FirstOrDefault();

      }
      /// <summary>
      /// 状态的修改
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int State_Edit(State s)
      {
          con.Open();
          string sql = "Update State set  StateName='" + s.StateName + "'";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }
      /// <summary>
      /// 状态的搜索
      /// </summary>
      /// <param name="RoleId"></param>
      /// <returns></returns>
      public List<State> State_Search(string StateName)
      {
          con.Open();
          string sql = "select StateId,StateName from State where StateName like'" + StateName + "'";
          SqlDataAdapter adp = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<State> list = new List<State>();
          foreach (DataRow item in table.Rows)
          {
              State s = new State();
              s.StateId = Convert.ToInt32(item["StateId"]);
              s.StateName = item["StateName"].ToString();
              list.Add(s);
          }
          return list;

      }





  }
}

